package com.zzmcc.jms.controller;

import com.jfinal.aop.Before;
import com.jfinal.ext.render.excel.JxlsRender;
import com.jfinal.kit.PathKit;
import com.jfinal.log.Logger;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.zzmcc.base.BaseController;
import com.zzmcc.common.AuthorizeInterceptor;
import com.zzmcc.jms.model.*;
import com.zzmcc.sys.model.Userinfo;
import org.apache.commons.lang.text.StrTokenizer;
import util.ChristStringUtil;
import util.DateUtil;
import util.ModelUtils;
import util.StringUtil;

import java.util.*;

public class DemandBuildController extends BaseController {
    private static final Logger log =  Logger.getLogger(DemandBuildController.class);
	private  int flag=0;
	public void index() {
		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		String name = getPara("name");
		String ctName = getPara("ctName");
		String cusName = getPara("cusName");
		String homeCounty = getPara("homeCounty");
		String isConfirm = getPara("isConfirm");
		String isProject = getPara("isProject");
		String buildType = getPara("buildType");
		String taskMonth = getPara("taskMonth");
		String isDesign = getPara("isDesign");
		String isReach = getPara("isReach");
		String isStart = getPara("isStart");
		String isComplete = getPara("isComplete");
		String isArchive = getPara("isArchive");
		String isSubmit = getPara("isSubmit");
		String demandMemo = getPara("demandMemo");
		String modifyDate = getPara("modifyDate");
		String isName = getPara("isName");
		String isAll = getPara("isAll");

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and a.name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and a.demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(taskMonth)){
			whee.append(" and a.taskMonth = ?");
			param.add(taskMonth);
		}

		if(ChristStringUtil.isNotEmpty(ctName)){
			whee.append(" and COALESCE(d.ctName,a.ctName)  like ?");
			param.add("%" + ctName + "%");
		}


		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and a.cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and a.homeCounty = ?");
			param.add(homeCounty);
		}

		if(ChristStringUtil.isNotEmpty(buildType)){
			whee.append(" and c.buildType = ?");
			param.add(buildType);
		}

		if(ChristStringUtil.isNotEmpty(isConfirm)){
			whee.append(" and coalesce(a.isConfirm,'否')= ?");
			param.add(isConfirm);
		}

		if(ChristStringUtil.isNotEmpty(isProject)){
			whee.append(" and coalesce(e.isProject,'否')= ?");
			param.add(isProject);
		}
		if(ChristStringUtil.isNotEmpty(isDesign)){
			whee.append(" and coalesce(b.isDesign,'否') = ?");
			param.add(isDesign);
		}

		if(ChristStringUtil.isNotEmpty(isReach)){
			whee.append(" and coalesce(b.isReach,'否') = ?");
			param.add(isReach);
		}
		if(ChristStringUtil.isNotEmpty(isName)){
			if (isName.equals("是")){
				whee.append(" and b.reachDate<NOW()  ");
			}else{
				whee.append(" and b.reachDate is NULL");
			}
		}

		if(ChristStringUtil.isNotEmpty(isStart)){
			whee.append(" and coalesce(b.isStart,'否') = ?");
			param.add(isStart);
		}
		if(ChristStringUtil.isNotEmpty(isComplete)){
			whee.append(" and coalesce(b.isComplete,'否') = ?");
			param.add(isComplete);
		}
		if(ChristStringUtil.isNotEmpty(isSubmit)){
			whee.append(" and coalesce(b.isSubmit,'否') = ?");
			param.add(isSubmit);
		}
		if(ChristStringUtil.isNotEmpty(isArchive)){
			whee.append(" and coalesce(b.isArchive,'否') = ?");
			param.add(isArchive);
		}

		if(ChristStringUtil.isNotEmpty(modifyDate)){
			whee.append(" and TO_DAYS(b.modifyDate) >= TO_DAYS(?)");
			param.add( modifyDate );
		}
		String where=" from jms_demand a left join jms_project_build b on b.pid=a.pid \n" +
				"left join jms_demand_screen c  on a.did=c.did\n" +
				"left join jms_demand_order d on a.did=d.did \n" +
				"left join jms_project e on a.pid=e.pid\n" +
				"where COALESCE(a.isPlus,'否')='否' and a.isValid='是'  and COALESCE(a.isCancel,'否')='否'  "+ whee.toString()+" order by b.modifyDate desc";
		Page<ProjectBuild> pager = ProjectBuild.dao.paginate(
				getParaToInt("pageNum", 1),
				getParaToInt("numPerPage", 20),
				"select a.id,a.cusName,a.homeCounty,a.name,a.longitude,a.latitude,a.addr,a.isConfirm,a.demandMemo,c.buildType,COALESCE(d.ctName,a.ctName) ctName,e.projectDate,e.isProject," +
						"b.isDesign,b.isReach,b.isStart,isComplete,isPass,isSubmit,b.submitDate,isArchive,b.reachDate," +
						"coalesce(b.designDate,date_add(b.reachDate,INTERVAL 3 day)) designDate,\n" +
						"coalesce(b.startDate,date_add(b.reachDate,INTERVAL 8 day)) startDate,\n" +
						"coalesce(b.completeDate,date_add(b.reachDate,INTERVAL 50 day)) completeDate,\n" +
						"coalesce(b.submitDate,date_add(b.reachDate,INTERVAL 55 day)) submitDate," +
						"b.modifyDate,b.modifyMemo,b.memo buildMemo \n",
				" from jms_demand a left join jms_project_build b on b.pid=a.pid \n" +
						"left join jms_demand_screen c  on a.did=c.did\n" +
						"left join jms_demand_order d on a.did=d.did \n" +
						"left join jms_project e on a.pid=e.pid\n" +
						"where COALESCE(a.isPlus,'否')='否' and a.isValid='是'  and COALESCE(a.isCancel,'否')='否'  "+ whee.toString()+" order by b.modifyDate desc",
				param.toArray());

		setAttr("pager",pager);
		setAttr("name",name);
		setAttr("ctName",ctName);
		setAttr("cusName",cusName);
		setAttr("homeCounty",homeCounty);
		setAttr("isConfirm",isConfirm);
		setAttr("isProject",isProject);
		setAttr("buildType",buildType);
		setAttr("taskMonth",taskMonth);
		setAttr("demandMemo",demandMemo);
		setAttr("isDesign",isDesign);
		setAttr("isReach",isReach);
		setAttr("isStart",isStart);
		setAttr("isComplete", isComplete);
		setAttr("isArchive",isArchive);
		setAttr("isSubmit",isSubmit);
		setAttr("modifyDate",modifyDate);
		setAttr("isName",isName);
		setAttr("isAll",isAll);

		render("../demand_build_list.html");
	}

	@Before(AuthorizeInterceptor.class)
	public void add() {
		String id = getPara("id");
		Userinfo loginUser =getSessionAttr("loginUser");
		String homeCounty=loginUser.get("homeCounty");
		String modifyUser=loginUser.get("realname");
		int roleid=loginUser.get("roleid");

		String sql="select a.name,a.pid,a.homeCounty," +
				"b.id,b.isReach,isDesign,b.isStart,b.isComplete,b.isPass,b.isSubmit," +
				"COALESCE(b.addrDate,NOW()) addrDate, " +
				"coalesce(b.designDate,date_add(b.reachDate,INTERVAL 3 day)) designDate,\n" +
				"coalesce(b.startDate,date_add(b.reachDate,INTERVAL 8 day)) startDate,\n" +
				"coalesce(b.completeDate,date_add(b.reachDate,INTERVAL 46 day)) completeDate,\n" +
				"coalesce(b.submitDate,date_add(b.reachDate,INTERVAL 56 day)) submitDate," +
				"b.modifyMemo,b.memo,reachDate,b.isGuard,b.guardReason,addrType," +
				"c.id as cid,c.buildType \n" +
				" from jms_demand a left join jms_project_build b on b.pid=a.pid \n " +
				"left join jms_demand_screen c on c.did=a.did " +
				" where a.id=?";
		ProjectBuild m= ProjectBuild.dao.findFirst(sql,id);
		//管理员及区域负责人才有权限修改
		if (roleid==0||(StringUtil.isNotEmpty(homeCounty)&&homeCounty.equals(m.get("homeCounty")))
				||(m.get("homeCounty").equals("台投")&&modifyUser.equals("李育纯"))){
			setAttr("modifyUser",modifyUser);
			setAttr("roleid",roleid);
			setAttr("projectBuild",m);
			render("../demand_bulid_input.html");
		} else {
			render("../project_operate_tip.html");
		}
	}

	public void save() {
		try {
			ProjectBuild m = getModel(ProjectBuild.class, "projectBuild");
			//String id = m.get("id").toString();
			//更新状态，同时更新完成时间

			String isReach = m.getStr("isReach");
			if (StringUtil.isNotEmpty(isReach) && isReach.equals("是")){
				ModelUtils.fillForUpdate(m,"reachDate");
			}

			String isDesign = m.getStr("isDesign");
			if (StringUtil.isNotEmpty(isDesign) && isDesign.equals("是")){
				m.set("designDate", new Date());
			}
			String isStart = m.getStr("isStart");
			if (StringUtil.isNotEmpty(isStart) && isStart.equals("是")){
				m.set("startDate", new Date());
			}
			String isComplete = m.getStr("isComplete");
			if (StringUtil.isNotEmpty(isComplete) && isComplete.equals("是")) {
				m.set("completeDate", new Date());
			}
			String isSubmit = m.getStr("isSubmit");
			if (StringUtil.isNotEmpty(isSubmit) && isSubmit.equals("是")){
				m.set("submitDate", new Date());
			}
			ModelUtils.fillForUpdate(m);
			if (m.get("id")==null)
			{
				//更新选址渠道时间
				m.set("addrDate", new Date());
				m.save();
			}
			else
				m.update();

			//更新建设方式
			DemandScreen s = getModel(DemandScreen.class,"demandScreen");
			s.update();

			renderDWZSuccessJson("施工进度更新成功！");
		}catch (Exception e){
			renderDWZSuccessJson("更新失败！确认是否筛查。");
		}
	}


	@Before(AuthorizeInterceptor.class)
	public void name() {
		String id = getPara("id");

		String sql="select a.id as aid,a.cusName,a.name,a.ctName,a.longitude,a.latitude," +
				"b.id as bid,b.isReach,b.addrName,b.addrAddr,b.addrLong,b.addrLat," +
				"c.isSuccess,c.failMemo,c.isShare,c.shareName,c.longitude as cLong,c.latitude as cLat," +
				"d.id as did,d.ctName as dName,d.buildType,d.longitude as dLong,d.latitude as dLat \n" +
				"from jms_demand a left join jms_project_build b on b.pid=a.pid \n" +
				"left join jms_demand_screen c on a.did=c.did \n" +
				"left join jms_demand_order d on a.did=d.did \n" +
				" where a.id=?";
		ProjectBuild m= ProjectBuild.dao.findFirst(sql,id);

		setAttr("projectBuild",m);
		render("../demand_name_input.html");

	}

	public  void named(){
		try {
			//更新谈点完成时间
			ProjectBuild m = getModel(ProjectBuild.class, "projectBuild");
			String isUpdate = getPara("isUpdate");
			if (StringUtil.isNotEmpty(isUpdate) && isUpdate.equals("是")){
				ModelUtils.fillForUpdate(m,"reachDate");
			}
			m.update();

			//更新(订单)铁塔名称
			DemandOrder s = getModel(DemandOrder.class, "demandOrder");
			s.update();

			renderDWZSuccessJson("标准命名成功！");
		}catch (Exception e){
			renderDWZSuccessJson("更新失败，请联系管理员！");
		}
	}

	/**
	 *
	 * 清单导出
	 */
	public void export(){

		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		//导出条件
		String name = getPara("name");
		String cusName = getPara("cusName");
		String homeCounty = getPara("homeCounty");
		String buildType = getPara("buildType");
		String taskMonth = getPara("taskMonth");
		String demandMemo = getPara("demandMemo");

		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and a.name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and a.demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and a.cusName = ?");
			param.add(cusName);
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and a.homeCounty = ?");
			param.add(homeCounty);
		}

		if(ChristStringUtil.isNotEmpty(buildType)){
			whee.append(" and c.buildType = ?");
			param.add(buildType);
		}

		if(ChristStringUtil.isNotEmpty(taskMonth)){
			whee.append(" and a.taskMonth = ?");
			param.add(taskMonth);
		}
		if (flag==0){
			whee.append(" and COALESCE(a.isPlus,'否')='否' ");
		}

		/**
		 * 清单
		 */
		String sql="select a.homeCounty,a.cusName,a.name,a.demandMemo,a.taskMonth,a.isConfirm,a.isCancel,a.createDate demandDate,a.longitude,a.latitude,a.addr,\n" +
				"b.isReach,b.reachDate,b.addrType,b.addrDate,b.isStart,b.startDate,b.isDesign,b.designDate,b.isComplete,b.completeDate,b.isSubmit,b.submitDate,b.isArchive,b.modifyDate,b.modifyMemo,b.memo,\n" +
				"date_add(b.reachDate,INTERVAL 3 day) pdesignDate,date_add(b.reachDate,INTERVAL 8 day) pstartDate,date_add(b.reachDate,INTERVAL 50 day) pcompleteDate,date_add(b.reachDate,INTERVAL 55 day) psubmitDate,\n" +
				"c.isOrder,c.createDate orderDate,c.submitDate ysubmitDate,COALESCE(c.ctName,a.ctName) ctName,c.productType,\n" +
				"d.isProject,d.projectDate,d.code,e.buildType\n" +
				" from jms_demand a\n" +
				"left join jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				" left join jms_demand_order c on a.did=c.did \n" +
				"LEFT JOIN jms_project d\n" +
				"on a.pid=d.pid \n" +
				" left join jms_demand_screen e on a.did=e.did \n" +
				"where  a.isValid='是'  and COALESCE(a.isCancel,'否')='否'  "+ whee.toString()+"  order by b.modifyDate desc" ;

		List<Record> buildDetail = Db.find(sql, param.toArray());

		/**
		 * 区县汇总"where  a.isValid='是'  "+ whee.toString()+"  order by b.modifyDate desc" ;
		 */
		sql = "select date_format(now(),'%Y%m%d') sumDate,cusName,homeCounty,cnt demandCnt,confirmCnt,regCnt,cancelCnt,\n" +
				"reachCnt,reachCnt/(cnt-cancelCnt) reachRate,\n" +
				"designCnt,designCnt/(cnt-cancelCnt) designRate,\n" +
				"startCnt,startCnt/(cnt-cancelCnt) startRate,\n" +
				"completeCnt,completeCnt/(cnt-cancelCnt) completeRate,\n" +
				"submitCnt,submitCnt/(cnt-cancelCnt) submitRate,\n" +
				"designDelay,startDelay,completeDelay,submitDelay\n" +
				"from (\n" +
				"select a.cusName,a.homeCounty,count(*) cnt,\n" +
				"sum(case when a.isConfirm='是' then 1 else 0 end) confirmCnt,\n" +
				"sum(case when c.isProject='是' then 1 else 0 end) regCnt,\n" +
				"sum(case when a.isCancel='是' then 1 else 0 end) cancelCnt,\n" +
				"sum(case when b.isReach='是' then 1 else 0 end) reachCnt,\n" +
				"sum(case when b.isDesign='是' then 1 else 0 end) designCnt,\n" +
				"sum(case when b.isStart='是' then 1 else 0 end) startCnt,\n" +
				"sum(case when b.isComplete='是' then 1 else 0 end) completeCnt,\n" +
				"sum(case when b.isSubmit='是' then 1 else 0 end) submitCnt,\n" +
				"sum(case when date_add(b.reachDate,INTERVAL 3 day)<COALESCE(b.designDate,now()) then 1 else 0 end) designDelay,\n" +
				"sum(case when date_add(b.reachDate,INTERVAL 8 day)<COALESCE(b.startDate,now()) then 1 else 0 end) startDelay,\n" +
				"sum(case when date_add(b.reachDate,INTERVAL 50 day)<COALESCE(b.completeDate,now()) then 1 else 0 end) completeDelay,\n" +
				"sum(case when date_add(b.reachDate,INTERVAL 55 day)<COALESCE(b.submitDate,now()) then 1 else 0 end) submitDelay\n" +
				"from jms_demand a left join jms_project_build b\n" +
				"on a.pid=b.pid\n " +
				"left join jms_project c on a.pid=c.pid \n" +
				"where  a.isValid='是'   and COALESCE(a.isCancel,'否')='否'  "+ whee.toString()+
				"group by a.cusName,a.homeCounty\n" +
				")t\n" +
				"\n";

		List<Record> buildRecords = Db.find(sql,param.toArray());
		String sumDate = DateUtil.getStringDateShort();
		Db.update("delete a from jms_project_build_sum a where sumDate = ? AND cusName = ?",sumDate,cusName);
		for(Record r:buildRecords){
			Db.save("jms_project_build_sum",r);
		}

		/*
   		1、完工率
   		*/
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) sort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate =? and cusName = ? \n" +
				"ORDER BY completeRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.completeSort = b.sort\n" +
				"where sumDate =? and cusName = ? ";
		Db.update(sql,sumDate,cusName,sumDate,cusName);

		/*
   		2、交付率
   		*/
		sql ="update jms_project_build_sum a\n" +
				"INNER JOIN\n" +
				"(select homeCounty, (@rowno:=@rowno+1) sort from jms_project_build_sum m,(select (@rowno:=0) ) n\n" +
				"where sumDate =? and cusName = ? \n" +
				"ORDER BY submitRate desc) b\n" +
				"on a.homeCounty = b.homeCounty\n" +
				"set a.submitSort = b.sort\n" +
				"where sumDate =? and cusName = ? ";
		Db.update(sql,sumDate,cusName,sumDate,cusName);

		/**
		 *点评
		 */
		String formerDate = DateUtil.getFormerDate(DateUtil.getStringDate());
		sql = "select a.confirmCnt,(a.confirmCnt-b.confirmCnt) addConfirmCnt,a.reachCnt,a.reachRate,(a.reachCnt-b.reachCnt) addReachCnt,\n" +
				"a.designCnt,a.designRate,(a.designCnt-b.designCnt) addDesignCnt, \n" +
				"a.startCnt,a.startRate,(a.startCnt-b.startCnt) addStartCnt, \n" +
				"a.completeCnt,a.completeRate,(a.completeCnt-b.completeCnt) addCompleteCnt, \n" +
				"a.submitCnt,a.submitRate,(a.submitCnt-b.submitCnt) addSubmitCnt \n" +
				"from (select sum(confirmCnt) confirmCnt,sum(reachCnt) reachCnt,sum(reachCnt)/sum(regCnt) reachRate,\n" +
				"sum(designCnt) designCnt,sum(designCnt)/sum(regCnt) designRate,\n" +
				"sum(startCnt) startCnt,sum(startCnt)/sum(startCnt) startRate,\n" +
				"sum(completeCnt) completeCnt,sum(completeCnt)/sum(regCnt),completeRate,\n" +
				"sum(submitCnt) submitCnt,sum(submitCnt)/sum(regCnt),submitRate\n" +
				"  from jms_project_build_sum\n" +
				"where cusName = '"+cusName+"' AND sumDate=" + sumDate + ") a,\n" +
				"(select sum(confirmCnt) confirmCnt,sum(reachCnt) reachCnt,sum(reachCnt) /sum(regCnt) reachRate,\n" +
				"sum(designCnt) designCnt,sum(designCnt) /sum(regCnt) designRate,\n" +
				"sum(startCnt) startCnt,sum(startCnt) /sum(startCnt) startRate,\n" +
				"sum(completeCnt) completeCnt,sum(completeCnt) /sum(regCnt),completeRate,\n" +
				"sum(submitCnt) submitCnt,sum(submitCnt) /sum(regCnt),submitRate \n" +
				"  from jms_project_build_sum\n" +
				"where cusName = '"+cusName+"' AND sumDate=" + formerDate +
				") b";
		List<Record> buildComment = Db.find(sql);

		/*
		sql="select concat(max(reach1),'、',max(reach2),'、',max(reach3)) reach,\n" +
				"concat(max(design1),'、',max(design2),'、',max(design3)) design,\n" +
				"concat(max(start1),'、',max(start2),'、',max(start3)) start,\n" +
				"concat(max(complete1),'、',max(complete2),'、',max(complete3)) complete,\n" +
				"concat(max(submit1),'、',max(submit2),'、',max(submit3)) submit\n" +
				"from (\n" +
				"select case when a.reachSort=1 then homeCounty  end reach1 ,\n" +
				" case when reachSort=2 then homeCounty  end  reach2,\n" +
				" case when reachSort=3 then homeCounty  end  reach3,\n" +
				" case when designSort=1 then homeCounty  end design1,\n" +
				" case when designSort=2 then homeCounty  end  design2,\n" +
				" case when designSort=3 then homeCounty  end  design3,\n" +
				" case when startSort=1 then homeCounty  end start1,\n" +
				" case when startSort=2 then homeCounty  end  start2,\n" +
				" case when startSort=3 then homeCounty  end  start3,\n" +
				" case when completeSort=1 then homeCounty  end complete1,\n" +
				" case when completeSort=2 then homeCounty  end complete2,\n" +
				" case when completeSort=3 then homeCounty  end  complete3,\n" +
				" case when submitSort=1 then homeCounty  end submit1,\n" +
				" case when submitSort=2 then homeCounty  end  submit2,\n" +
				" case when submitSort=3 then homeCounty  end  submit3\n" +
				"from jms_project_build_sum a\n" +
				"where cusName = ? AND a.sumDate= ? \n" +
				" )t";
		List<Record> buildSort = Db.find(sql,cusName,sumDate);
		*/


		//完工率后3名
		sql="select homeCounty from jms_project_build_sum a where a.sumDate = ? and a.cusName=? \n" +
			"ORDER BY completeRate LIMIT 0,3";
		List<Record> completeSort = Db.find(sql,sumDate,cusName);
		String completeSortCounty = "";
		for(Record r:completeSort){
			completeSortCounty=completeSortCounty+'、'+r.get("homeCounty");
		}

		//交付率后3名
		sql="select homeCounty from jms_project_build_sum a where a.sumDate = ? and a.cusName=? \n" +
				"ORDER BY completeRate LIMIT 0,3";
		List<Record> submitSort = Db.find(sql,sumDate,cusName);
		String submitSortCounty = "";
		for(Record r:submitSort){
			submitSortCounty=submitSortCounty+'、'+r.get("homeCounty");
		}

		//进度更新的区县
		sql="select distinct homeCounty  as homeCounty \n" +
				"from jms_demand a LEFT JOIN jms_project_build b on a.pid=b.pid\n" +
				"where cusName = ? AND TO_DAYS(b.modifyDate) >= TO_DAYS(?)";
		List<Record> buildCounty = Db.find(sql,cusName,sumDate);
		String updateCounty = "";
		for(Record r:buildCounty){
			updateCounty=updateCounty+'、'+r.get("homeCounty");
		}

		sql="select * from jms_project_build_sum a where cusName = ? AND sumDate = ?";
		List<Record> buildSum = Db.find(sql,cusName,sumDate);
		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("buildDetail", buildDetail);
		beans.put("buildSum", buildSum);

		beans.put("sumDate",sumDate);
		beans.put("cusName",cusName);
		beans.put("homeCounty",homeCounty);
		beans.put("buildComment",buildComment);
		beans.put("updateCounty",updateCounty);
		beans.put("completeSortCounty",completeSortCounty);
		beans.put("submitSortCounty",submitSortCounty);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_project_build.xls";
		String filename = "project_build.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}


	public void exportAll(){
		flag=1;
		export();
	}


	public void exportCt(){

		StringBuffer whee = new StringBuffer();
		List<Object> param = new ArrayList<Object>();

		//导出条件
		String name = getPara("name");
		String cusName = getPara("cusName");
		String homeCounty = getPara("homeCounty");
		String buildType = getPara("buildType");
		String taskMonth = getPara("taskMonth");
		String demandMemo = getPara("demandMemo");


		if(ChristStringUtil.isNotEmpty(name)){
			whee.append(" and a.name like ?");
			param.add("%" + name + "%");
		}
		if(ChristStringUtil.isNotEmpty(demandMemo)){
			whee.append(" and a.demandMemo like ?");
			param.add("%" + demandMemo + "%");
		}

		if(ChristStringUtil.isNotEmpty(cusName)){
			whee.append(" and cusName like ?");
			param.add("%" + cusName + "%");
		}

		if(ChristStringUtil.isNotEmpty(homeCounty)){
			whee.append(" and homeCounty = ?");
			param.add(homeCounty);
		}

		if(ChristStringUtil.isNotEmpty(buildType)){
			whee.append(" and c.buildType = ?");
			param.add(buildType);
		}

		if(ChristStringUtil.isNotEmpty(taskMonth)){
			whee.append(" and a.taskMonth = ?");
			param.add(taskMonth);
		}

		/**
		 * 清单
		 */
		String sql="select a.homeCounty,a.cusName,a.name,a.taskMonth,a.isConfirm,a.isCancel,\n" +
				"b.isReach,b.addrType,b.isStart,b.isDesign,b.isComplete,b.isSubmit,b.isArchive,b.modifyDate,b.modifyMemo,b.memo,\n" +
				"COALESCE(c.ctName,a.ctName) ctName,c.longitude,c.latitude,c.addr,\n" +
				"d.isProject,d.projectDate,d.code,e.buildType\n" +
				" from jms_demand a\n" +
				"left join jms_project_build b\n" +
				"on a.pid=b.pid\n" +
				" left join jms_demand_order c on a.did=c.did \n" +
				"LEFT JOIN jms_project d\n" +
				"on a.pid=d.pid\n" +
				"left join jms_demand_screen e on a.did=e.did \n" +
				"where 1=1  "+ whee.toString()+"  order by b.modifyDate desc" ;

		List<Record> buildDetail = Db.find(sql, param.toArray());

		/**
		 * 区县汇总
		 */
		sql = "select date_format(now(),'%Y%m%d') sumDate,homeCounty,(cnt-cancelCnt) cnt,(cnt-confirmCnt) unconfirm,cancelCnt,\n" +
				"shareCnt,shareReachCnt,newCnt,(newReach-ctReach) ttReach,ctReach,(newReachCnt-ctReachCnt) ttReachCnt,ctReachCnt,completeCnt,submitCnt\n" +
				"from (\n" +
				"select a.homeCounty,count(*) cnt,\n" +
				"sum(case when c.buildType in('独建','共建','共享') then 1 else 0 end) confirmCnt,\n" +
				"sum(case when c.buildType in('共建','共享') then 1 else 0 end) shareCnt,\n" +
				"sum(case when c.buildType in('共建','共享')  and b.isReach='是' then 1 else 0 end) shareReachCnt,\n" +
				"sum(case when c.buildType in('独建') then 1 else 0 end) newCnt,\n" +
				"sum(case when c.buildType in('独建') and b.addrType not in('电信反向委托') then 1 else 0 end) newReach,\n" +
				"sum(case when c.buildType in('独建') and b.addrType='电信反向委托' then 1 else 0 end) ctReach,\n" +
				"sum(case when c.buildType in('独建') and B.isReach='是' then 1 else 0 end) newReachCnt,\n" +
				"sum(case when c.buildType in('独建') and B.isReach='是' and b.addrType='电信反向委托' then 1 else 0 end) ctReachCnt,\n" +
				"sum(case when a.isCancel='是' then 1 else 0 end) cancelCnt,\n" +
				"sum(case when b.isComplete='是' then 1 else 0 end) completeCnt,\n" +
				"sum(case when b.isSubmit='是' then 1 else 0 end) submitCnt\n" +
				"from jms_demand a left join jms_project_build b\n" +
				"on a.pid=b.pid \n" +
				"left join jms_demand_screen c on a.did=c.did " +
				"where 1=1  "+ whee.toString()+
				"group by a.homeCounty\n" +
				")t\n" +
				"\n";

		List<Record> buildSum = Db.find(sql,param.toArray());
		Map<String, Object> beans = new HashMap<String, Object>();
		beans.put("buildDetail", buildDetail);
		beans.put("buildSum", buildSum);

		String templateFileName = PathKit.getRootClassPath() + "/tpl_ct_project_build.xls";
		String filename = "ct_project_build.xls";
		render(JxlsRender.me(templateFileName).filename(filename).beans(beans));
	}

	@Before(AuthorizeInterceptor.class)
	public void delete() {
		String[] ids = getParaValues("ids");
		for(String id:ids){
			Project m =Project.dao.findById(id);
			Db.update("delete a from jms_project_build a where pid =?",m.get("pid"));
		}
		renderDWZSuccessJson("清除成功!");
	}

	/**
	 * 项目交付
	 */
	@Before(AuthorizeInterceptor.class)
	public void submit()
	{
		String[] ids = getParaValues("ids");
		for (String id : ids) {
			try
			{
				String sql = "select a.pid,b.id,coalesce(b.isSubmit,'否') isSubmit" +
						" from jms_demand a left join jms_project_build b on b.pid=a.pid  where a.id=?";
				ProjectBuild m = ProjectBuild.dao.findFirst(sql,id);
				String isSubmit = m.getStr("isSubmit");
				if (isSubmit.equals("否")) {
					m.set("isSubmit", "是");
					m.set("submitDate",new Date());
				}
				ModelUtils.fillForUpdate(m);
				m.update();

			}
			catch (Exception e)
			{
				renderDWZSuccessJson("建设未完成，不能归档！");
				return;
			}
		}
		renderDWZSuccessJson("交付成功成功!");
	}

	/**
	 * 建设归档
	 */
	@Before(AuthorizeInterceptor.class)
	public void archive()
	{
		String[] ids = getParaValues("ids");
		for (String id : ids) {
			try
			{
				String sql = "select a.pid,b.id,coalesce(b.isArchive,'否') isArchive" +
						" from jms_demand a left join jms_project_build b on b.pid=a.pid  where a.id=?";
				ProjectBuild m = ProjectBuild.dao.findFirst(sql,id);
				String isArchive = m.getStr("isArchive");
				if (isArchive.equals("否")) {
					m.set("isArchive", "是");
				}
				ModelUtils.fillForUpdate(m);
				m.update();

			}
			catch (Exception e)
			{
				renderDWZSuccessJson("未完成，不能归档！");
				return;
			}
		}
		renderDWZSuccessJson("归档成功!");
	}
}